package com.example.repository;

import java.util.List;
import java.util.Map;

import com.example.model.MachineModel;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import com.example.entity.Machine;


/**
 * Created by Administrator on 2019/5/29.
 */
public interface MachineRepository extends JpaRepository<Machine, Long>,JpaSpecificationExecutor<Machine> {

    Machine findByImei(String imei);

    @Query(value="select u.account as account,m.id as machineId,m.imei as imei,p.product_type_name as productTypeName,m.status as status,m.user_status as userStatus,m.activation_time as activationTime from machine m left join user u on m.user_id = u.id left join product_type p on m.product_type = p.id where m.imei like CONCAT('%',?1,'%')",nativeQuery = true)
    List<Map<String,String>> findMachineResultByImei(String imei);

    @Query(value="select u.account as account,m.id as machineId,m.imei as imei,p.product_type_name as productTypeName,m.status as status,m.user_status as userStatus,m.activation_time as activationTime from machine m left join user u on m.user_id = u.id left join product_type p on m.product_type = p.id where u.account like CONCAT('%',?1,'%')",nativeQuery = true)
    List<Map<String,String>> findMachineResultByAccount(String account);

    @Query(value="select id as id,group_type as groupType,user_status as userStatus,status as status,product_type as productType from machine where id=?1",nativeQuery = true)
    List<Map<String,String>> findEditMachie(int machineId);

    @Query(value="select new com.example.model.MachineModel(u.account,m.id,m.imei,m.productType,p.productTypeName,m.status,m.userStatus,m.activationTime) from Machine m left join User u on m.userId = u.id left join ProductType p on m.productType = p.id where m.imei like CONCAT('%',?1,'%')")
//    @Query(value="select new com.example.model.MachineModel(u.account,m.id,m.imei,m.productType,p.productTypeName,m.status,m.userStatus,m.activationTime) from Machine m left join User u on m.userId = u.id left join ProductType p on m.productType = p.id where m.imei = ?1")
    List<MachineModel> selectModelByImei(String imei);

    @Query(value="select new com.example.model.MachineModel(u.account,m.id,m.imei,m.productType,p.productTypeName,m.status,m.userStatus,m.activationTime) from Machine m left join User u on m.userId = u.id left join ProductType p on m.productType = p.id where m.imei like CONCAT('%',?1,'%')")
    Page<MachineModel> selectPageByImei(String imei, Pageable pageable);

    @Query(value="select new com.example.model.MachineModel(u.account,m.id,m.imei,m.productType,p.productTypeName,m.status,m.userStatus,m.activationTime) from Machine m left join User u on m.userId = u.id left join ProductType p on m.productType = p.id where u.account like CONCAT('%',?1,'%')")
    Page<MachineModel> selectPageByAccount(String account,Pageable pageable);

}
